//
//  HLSqliteManager.swift
//  dataBase
//
//  Created by helong on 16/7/23.
//  Copyright © 2016年 helong. All rights reserved.
//
/// 数据库管理工具
import UIKit
//  数据库的名称
private let dbName = "weibo.db"
//  数据库的路径
private let path = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)

class HLSqliteManager: NSObject {
    //  单例
    static let manager = HLSqliteManager()
    //  懒加载全局操作队列,是线程安全的
    let queue = FMDatabaseQueue(path: path)
    //  构造函数私有化
    private override init() {
        super.init()
        //  根据sqlite语句创建数据库
        creatDateBase()
        
    }
    
     // MARK: - 创建数据库
    private func creatDateBase() {
        //  获取sqlite 语句所在的路径
        let sqlPath = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)
        //  根据路径创库建表
        //  try!  数据一定正确,否则崩溃
       let sql = try! String(contentsOfFile: sqlPath!)
        queue.inDatabase { (dataBase) -> Void in
            let result = dataBase.executeStatements(sql)
            if result {
                print("创表成功")
            } else {
                print("创表失败")
            }
        }
        print(path)
    }
    
     // MARK: - 增
    func insertData() {
        let sql = "INSERT INTO T_PERSON (NAME,AGE) VALUES(?, ?)"
        queue.inDatabase { (dataBase) -> Void in
          let result = dataBase.executeUpdate(sql, withArgumentsInArray: ["何龙", 20])
            if result {
                print("success")
            } else {
                print("failed")
            }
    }
    }
     // MARK: - 修改
    func updageData() {
        let sql = "UPDATE T_PERSON SET AGE = ? WHERE ID = ?"
        queue.inDatabase { (dataBase) -> Void in
           let result = dataBase.executeUpdate(sql, withArgumentsInArray: [18, 1])
            if result {
                print("success")
            } else {
                print("failed")
            }
        }
        
    }
     // MARK: - 删
    func deleteData() {
        let sql = "DELETE FROM T_PERSON  WHERE ID = ?"
        queue.inDatabase { (dataBase) -> Void in
            let result = dataBase.executeUpdate(sql, withArgumentsInArray: [1])
            if result {
                print("success")
            } else {
                print("failed")
            }
        }
    }
     // MARK: - 查询
    func query() {
        let sql = "SELECT * FROM T_PERSON"
        queue.inDatabase { (dataBase) -> Void in
          let resultSet = dataBase.executeQuery(sql, withArgumentsInArray: nil)
            while resultSet.next() {
                let name = resultSet.stringForColumn("NAME")
                let age = resultSet.intForColumn("AGE")
                print(name,age)
            }
        }
    }
     // MARK: - 查询
    func query1(sql: String) -> [[String: AnyObject]]{
        
        var dataArr = [[String: AnyObject]]()
        //  根据sql语句执行查询
        queue.inDatabase { (dataBase) -> Void in
            let resultSet = dataBase.executeQuery(sql, withArgumentsInArray: nil)
            //  将返回的记过存在字典中
            while resultSet.next() {
                var tempDict = [String: AnyObject]()
                for i in 0..<resultSet.columnCount() {
                    //键
                    let colName = resultSet.columnNameForIndex(i)
                    //值
                    let colValue = resultSet.objectForColumnIndex(i)
                    //字典
                    tempDict[colName] = colValue
                    //存入字典数组
                    dataArr.append(tempDict)
                }
            }
        }
        return dataArr
    }
    
}
